# coding:utf-8
from flask import Flask, request, session, \
    jsonify, redirect, render_template, send_from_directory, send_file, url_for
from flask_session import Session
from sqlmodel import select, insert, update, text, Relationship, create_engine, delete

from .config import ROOTDIR, IS_WINDOWS, TZ_shanghai
from .models import USER, PASSWORD, HOST, PORT, DATABASE, DATABASE_URL,\
    session_local, BookInfo, Permission, Role, User, PermissionEnum, RoleEnum, \
    SQLAlchemyError

app = Flask(__name__, template_folder = f'{ROOTDIR}/templates', static_folder = f'{ROOTDIR}/static')
app.config['SQLALCHEMY_DATABASE_URI'] = DATABASE_URL
# 存储信息的session(登录的用户信息)
app.config["SECRET_KEY"] = "yeyuan" # 用于加密和签名存储在会话中的数据
app.config["SESSION_PERMANENT"] = False # 决定 session 是否为永久性 False:会在浏览器关闭时失效
app.config["SESSION_TYPE"] = "filesystem" # 指定会话数据的存储方式 filesystem:会话数据将存储在服务器的文件系统
## 会话数据将存储在服务器的文件系统的相关配置
app.config["SESSION_FILE_DIR"] = f"{ROOTDIR}/log/flask_session" # 指定会话文件存储的目录
app.config["SESSION_FILE_THRESHOLD"] = 5  # 最大存储的会话文件数量
app.config["SESSION_FILE_MODE"] = 0o600  # 文件权限（八进制）
Session(app)


@app.route('/')
def index():
    """默认路由,直接跳转到登录路由"""    
    # 使用 redirect 和 url_for 将请求重定向到 /login
    return redirect(url_for('login'))


@app.route('/login',methods=['GET','POST'])
def login():
    """登录路由
    GET:登录页面,输入信息
    POST:提交登录信息,登录验证
    """
    method = request.method
    if method == 'GET':
        return render_template('login.html', url_for = url_for)
    elif method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        action = request.form.get('action')
        # 判断是登录还是注册
        if action == 'login':
            session['flag_login'] = False
            # 判断用户是否存在
            sql_ = select(User).where(User.username==username)
            res_ = session_local.execute(sql_).all()
            if len(res_) == 0:
                error_message = f'用户{username}不存在,请先注册'
                return render_template('login.html', url_for = url_for, error_message = error_message)
            else:
                # 判断用户密码是否正确
                sql_ = select(User).where(User.username==username).where(User.password==password)
                res_ = session_local.execute(sql_).all()
                if len(res_) == 0:
                    error_message = f'用户{username}密码错误'
                    return render_template('login.html', url_for = url_for, error_message = error_message)
                else:
                    session['flag_login'] = True
                    session['User'] = dict(res_[0][0])
                    return redirect(url_for('bookinfo'))
        elif action == 'register':
            if len(username) == 0 or ' ' in username:
                error_message = f'用户名中为空或含有空格,注册失败'
                return render_template('login.html', url_for = url_for, error_message = error_message)
            sql_ = select(User).where(User.username==username)
            res_ = session_local.execute(sql_).all()
            if len(res_) != 0:
                error_message = f'用户名{username}已存在,注册失败'
                return render_template('login.html', url_for = url_for, error_message = error_message)
            else:
                try:
                    new_user = User(username=username,password=password,role=RoleEnum.common)
                    session_local.add(new_user)
                    session_local.commit()
                    success_message = f'新用户{username}注册成功'
                    return render_template('login.html', url_for = url_for, success_message = success_message)
                except SQLAlchemyError:
                    session_local.rollback()
                    print("注册新用户失败")
                finally:
                    session_local.close()
   
@app.route('/bookinfo', methods=['GET','POST'])
def bookinfo():
    """书籍信息页面"""
    # 所有数据信息
    sql_ = select(BookInfo)
    res_ = session_local.execute(sql_).all()
    all_bookinfos = []
    for i in res_:
        all_bookinfos.append(i[0].to_dict()) 
    method = request.method
    if method == 'GET':
        return render_template('bookinfo.html', active_page='bookinfo', url_for = url_for, all_bookinfos = all_bookinfos)

@app.route('/search_books', methods=['POST'])
def search_books():
    search_bookinfos = []
    method = request.method
    if method == 'POST':
        post_data = request.json
        search_bookinfos.extend(search('title',post_data['title']))
        search_bookinfos.extend(search('author',post_data['author']))
        search_bookinfos.extend(search('published_year',post_data['published_year']))
        search_bookinfos = [i.to_dict() for i in set(search_bookinfos)]
        return jsonify(search_bookinfos)
        # return render_template('bookinfo.html', active_page='bookinfo', url_for = url_for, all_bookinfos = search_bookinfos)

def search(search_key,search_value):
    search_bookinfos = []
    if search_key == 'title':
        sql_ = select(BookInfo).where(BookInfo.title==search_value)
        res = session_local.execute(sql_).all()
    elif search_key == 'author':
        sql_ = select(BookInfo).where(BookInfo.author==search_value)
        res = session_local.execute(sql_).all()
    elif search_key == 'published_year':
        sql_ = select(BookInfo).where(BookInfo.published_year==search_value)
        res = session_local.execute(sql_).all()
    for i in res:
        search_bookinfos.append(i[0])
    return search_bookinfos

@app.route('/add_books', methods=['POST'])
def add_books():
    method = request.method
    if method == 'POST':
        user:User = session.get('User')
        # NOTE 权限
        if user['role'] == RoleEnum.common:
            error_message = f'用户{user["username"]}角色为{user["role"].value},没有权限'
            raise
        post_data = request.json
        new_bookinfo = BookInfo(
            title=post_data['title'],
            author=post_data['author'],
            isbn=post_data['isbn'],
            published_year=post_data['published_year'],
        )
        try:
            session_local.add(new_bookinfo)
            session_local.commit()
            print(f"添加新书籍{post_data['title']}数据成功")
        except SQLAlchemyError:
            session_local.rollback()
            print("数据库操作失败")
        finally:
            session_local.close()
    sql_ = select(BookInfo)
    res_ = session_local.execute(sql_).all()
    all_bookinfos = []
    for i in res_:
        all_bookinfos.append(i[0].to_dict())
    all_bookinfos = sorted(all_bookinfos,key=lambda x:x['id'],reverse=True)
    return jsonify(all_bookinfos)

@app.route('/delete_bookinfo/<int:book_id>', methods=['DELETE'])
def delete_bookinfo(book_id):
    user:User = session.get('User')
    # NOTE 权限
    if user['role'] != RoleEnum.root:
        error_message = f'用户{user["username"]}角色为{user["role"].value},没有权限'
        raise
    sql_ = select(BookInfo).where(BookInfo.id==book_id)
    book:BookInfo = session_local.execute(sql_).one()
    if not book:
        return jsonify({"error": "书籍不存在"}), 404
    book = book[0]
    sql_ = delete(BookInfo).where(BookInfo.id==book.id)
    session_local.execute(sql_)
    session_local.commit()
    return jsonify({"message": "删除成功"}), 200

@app.route('/getbookinfo/<int:book_id>', methods=['GET'])
def getbookinfo(book_id):
    sql_ = select(BookInfo).where(BookInfo.id==book_id)
    book:BookInfo = session_local.execute(sql_).one()
    if not book:
        return jsonify({"error": "书籍不存在"}), 404
    book = book[0]
    return jsonify({
        "id": book.id,
        "create_time": book.create_time,
        "title": book.title,
        "author": book.author,
        "isbn": book.isbn,
        "published_year": book.published_year,
        "note": book.note,
    })

@app.route('/updatebookinfo/<int:book_id>', methods=['PUT'])
def updatebookinfo(book_id):
    user:User = session.get('User')
    # NOTE 权限
    if user['role'] == RoleEnum.common:
        error_message = f'用户{user["username"]}角色为{user["role"].value},没有权限'
        raise 
    sql_ = select(BookInfo).where(BookInfo.id==book_id)
    book:BookInfo = session_local.execute(sql_).one()
    if not book:
        return jsonify({"error": "书籍不存在"}), 404
    book = book[0]
    data = request.json
    title = data.get("title")
    author = data.get("author")
    isbn = data.get("isbn")
    published_year = data.get("published_year")
    note = data.get("note")
    sql_ = update(BookInfo).where(BookInfo.id==book.id)\
        .values(title=title).values(author=author).values(isbn=isbn).values(published_year=published_year).values(note=note)
    session_local.execute(sql_)
    session_local.commit()
    return jsonify({"message": "更新成功"}), 200


@app.route('/user', methods=['GET','POST'])
def user():
    """用户信息页面"""
    method = request.method
    if method == 'GET':
        flag_login = session.get('flag_login')
        if flag_login:
            user = session.get('User')
            return render_template('user.html', active_page='user', user = user)
    elif method == 'POST':
        # 修改密码
        user = session.get('User')
        current_password = request.form.get('current_password')
        new_password = request.form.get('new_password')
        confirm_password = request.form.get('confirm_password')
        # NOTE user为dict类型 html中可以user.password访问
        if user['password'] != current_password:
            error_message = f'输入的当前密码不对'
            return render_template('user.html', url_for = url_for, user = user, error_message = error_message)
        elif new_password != confirm_password:
            error_message = f'输入的两次新密码不同'
            return render_template('user.html', url_for = url_for, user = user, error_message = error_message)
        else:
            sql_ = update(User).where(User.username==user['username']).where(User.password==user['password']) \
                .values(password=new_password)
            try:
                res_ = session_local.execute(sql_)
            except SQLAlchemyError:
                session_local.rollback()
            except Exception as error_message:
                return render_template('user.html', url_for = url_for, user = user, error_message = error_message)
            else:
                session_local.commit()
                success_message = f'修改密码成功'
                return render_template('user.html', url_for = url_for, user = user, success_message = success_message)
            finally:
                session_local.close()

@app.route('/logout', methods=['GET'])
def logout():
    """用户退出登录"""
    session.clear()
    return redirect(url_for('login'))


